-- =============================================
-- Author:		<Huynh Quoc Thanh>
-- Create date: <26/01/2010>
-- Description:	<Update status of specific timesheet record by ID>
-- =============================================
USE TimeSheet
GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'usp_GetTimeSheetByCondition')
	DROP PROCEDURE usp_GetTimeSheetByCondition
GO
-- usp_GetTimeSheetByCondition 0, 0, 0, null, null
CREATE PROCEDURE usp_GetTimeSheetByCondition
(
	@UserID BIGINT = 0,
	@ProjectID BIGINT = 0,
	@StatusID TINYINT = 0,
	@FromDate DATETIME = NULL,
	@ToDate DATETIME = NULL
)
AS
BEGIN

SELECT 	A.TimeSheetID,
		CONVERT(varchar, A.Date, 110) AS Date,
		A.Time,
		A.Description,
		B.ProductName,
		C.WorkName,
		D.ProcessName,
		E.StatusName,
		F.ProjectName
		G.Username
FROM TS_TimeSheet A 
	INNER JOIN TS_Product B
		ON A.ProductID = B.ProductID
	INNER JOIN TS_Work C
		ON A.WorkID = C.WorkID
	INNER JOIN TS_Process D
		ON A.ProcessID = D.ProcessID
	INNER JOIN TS_Status E
		ON A.StatusID = E.StatusID
	INNER JOIN PJ_Project F
		ON A.ProjectID = F.ProjectID
	INNER JOIN Users G
		ON A.UserID = G.UserID
WHERE (@UserID = 0 OR A.UserID = @UserID)
  AND (@ProjectID = 0 OR A.ProjectID = @ProjectID)
  AND (@StatusID = 0 OR A.StatusID = @StatusID)
  AND (@FromDate IS NULL OR (@ToDate IS NULL OR A.Date BETWEEN @FromDate AND @ToDate))
  
END